<?php
/**
  * sendsub model 文件
  * @author ml
  * @date 2017-07-10
  */
class M_sendsub extends MY_Model {
	
//	private $table = "bjnz_new_admin_object";
	function M_sendsub() {
		parent::__construct ();
	}

    //有条件查询列表
    function get_list($star,$end,$true,$limit,$where="",$go="t6.PID")
    {
        if($where == "")
        {
            $where = " where  t6.PID not in(1,62,63) ";
        }
        else
        {
            $where .= " AND  t6.PID not in(1,62,63) ";
        }
        if($true == 1)
        {
            $sql = "select count(*) from 
               (select t5.PID,t5.postid,t6.LoginName,d.name,t6.UserName as aname,t5.workname,t5.pname,i.Tel,i.Mobile,i.Email,sum(t5.sub) as sub,sum(t5.send) as send,sum(t5.anq) as anq,sum(t5.weis) as weis,sum(t5.wwc) as wwc,sum(t5.ywc) as ywc,sum(t5.xj) as xj,sum(t5.wx) as wx,sum(t5.tuogang) as tuogang from
t_user t6 left join
(
select t2.* FROM(
SELECT u.PID,t.postid,u.LoginName,w.name,s.aname,w.name as workname,w.pname,i.Tel,i.Mobile,i.Email,count(IF(s.workstatus>0,t.postid,null)) as sub,count(IF(s.workstatus=2,t.postid,null)) as send,0 as anq,0 as weis,0 as wwc,0 as ywc,0 as xj,0 as wx,0 as tuogang
				FROM  t_bjnz_train_task t 
				LEFT JOIN t_bjnz_train_task_sub s ON t.id = s.taskid  
			    LEFT JOIN t_bjnz_work w ON t.postid=w.id
                LEFT JOIN t_user u on s.accepitid=u.PID
                LEFT JOIN t_user_info i ON u.PID=i.PID
			    where t.status >3 and s.pubtime>=$star and s.pubtime<=$end
			    GROUP BY t.postid,s.accepitid order by s.aname desc) t2
UNION
select t10.* from
(
SELECT
 	m.adduid as PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,0 as wwc,0 as ywc ,0 as xj,0 as wx,count(m.wangge_id) as tuogang
FROM
	t_bjnz_device_maintenance m
LEFT JOIN t_bjnz_device d ON m.devid = d.id where m.wangge_id<>d.wangge_id and m.addtime>=$star and m.addtime<=$end
GROUP BY
	m.adduid
ORDER BY
	m.adduid) t10
UNION
select t3.* from
(
select rm.CID as PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,count(IF(Type<>3,true,null)) anq,count(IF(Type=3,true,null)) weis,0 as wwc,0 as ywc ,0 as xj,0 as wx,0 as tuogang
from t_bjnz_report_mess rm LEFT JOIN t_user t ON t.PID=rm.Cid where rm.CreateTime>=$star and rm.CreateTime<=$end GROUP BY Cid) t3
UNION
select t4.* from
(
select u.PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,count(IF(um.status=1,true,null)) as wwc,count(IF(um.status=2,true,NULL)) as ywc ,0 as xj,0 as wx,0 as tuogang
from bjnz_new_quantification_user_map um LEFT JOIN bjnz_new_quantification_user u on um.qu_user_id=u.id where um.start_time>=$star and um.start_time<=$end  GROUP BY u.PID ORDER BY u.PID) t4
UNION
select t7.* from (
select ss.pid,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,0 aswwc,0 as ywc ,count(if(ss.type=1,true,null)) as xj,count(if(ss.type=2,true,null)) as wx,0 as tuogang
                from t_bjnz_device_task_sub ss
                where ss.optime>=$star and ss.optime<=$end
                GROUP BY ss.pid
) t7
 ) t5 on t6.PID=t5.PID LEFT JOIN t_enterprise_dept d on t6.DepartmentID=d.ID LEFT JOIN t_user_info i on t6.PID=i.PID $where GROUP BY $go ORDER BY $go) as aa";
            $res = $this->query_count($sql);
        }
        else
        {
            if($limit != "")
            {
                $limit = " limit {$limit}";
            }
            else
            {
                $limit = "";
            }
            $sql = "select t5.PID,t5.postid,t6.LoginName,IF(d.Level<>1,d.Name,d.syncDepId) as name,t6.UserName as aname,t5.workname,t5.pname,i.Tel,i.Mobile,i.Email,sum(t5.sub) as sub,sum(t5.send) as send,sum(t5.anq) as anq,sum(t5.weis) as weis,sum(t5.wwc) as wwc,sum(t5.ywc) as ywc,sum(t5.xj) as xj,sum(t5.wx) as wx,sum(t5.tuogang) as tuogang,sum(t5.baoxiu) as baoxiu from
t_user t6 left join
(
select t2.* FROM(
SELECT u.PID,t.postid,u.LoginName,w.name,s.aname,w.name as workname,w.pname,i.Tel,i.Mobile,i.Email,count(IF(s.workstatus>0,t.postid,null)) as sub,count(IF(s.workstatus=2,t.postid,null)) as send,0 as anq,0 as weis,0 as wwc,0 as ywc,0 as xj,0 as wx,0 as tuogang,0 as baoxiu
				FROM  t_bjnz_train_task t 
				LEFT JOIN t_bjnz_train_task_sub s ON t.id = s.taskid  
			    LEFT JOIN t_bjnz_work w ON t.postid=w.id
                LEFT JOIN t_user u on s.accepitid=u.PID
                LEFT JOIN t_user_info i ON u.PID=i.PID
			    where t.status >3 and s.pubtime>=$star and s.pubtime<=$end
			    GROUP BY t.postid,s.accepitid order by s.aname desc) t2
UNION
select t10.* from
(
SELECT
 	m.adduid as PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,0 as wwc,0 as ywc ,0 as xj,0 as wx,count(m.wangge_id) as tuogang,count(IF(m.status>=0,true,null)) as baoxiu
FROM
	t_bjnz_device_maintenance m
LEFT JOIN t_bjnz_device d ON m.devid = d.id where m.wangge_id<>d.wangge_id and m.addtime>=$star and m.addtime<=$end
GROUP BY
	m.adduid
ORDER BY
	m.adduid) t10
UNION
select t3.* from
(
select rm.CID as PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,count(IF(Type<>3,true,null)) anq,count(IF(Type=3,true,null)) weis,0 as wwc,0 as ywc ,0 as xj,0 as wx,0 as tuogang,0 as baoxiu
from t_bjnz_report_mess rm LEFT JOIN t_user t ON t.PID=rm.Cid where rm.CreateTime>=$star and rm.CreateTime<=$end GROUP BY Cid) t3
UNION
select t4.* from
(
select u.PID,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,count(IF(um.status=1,true,null)) as wwc,count(IF(um.status=2,true,NULL)) as ywc ,0 as xj,0 as wx,0 as tuogang,0 as baoxiu
from bjnz_new_quantification_user_map um LEFT JOIN bjnz_new_quantification_user u on um.qu_user_id=u.id where um.start_time>=$star and um.start_time<=$end  GROUP BY u.PID ORDER BY u.PID) t4
UNION
select t7.* from (
select ss.pid,0 as postid,0 as LoginName,0 as name,0 as aname,0 as workname,0 as pname,0 as Tel,0 as Mobile,0 as Email,0 as sub,0 as send,0 as anq,0 as weis,0 aswwc,0 as ywc ,count(if(ss.type=1,true,null)) as xj,count(if(ss.type=2,true,null)) as wx,0 as tuogang,0 as baoxiu
                from t_bjnz_device_task_sub ss
                where ss.optime>=$star and ss.optime<=$end
                GROUP BY ss.pid
) t7
 ) t5 on t6.PID=t5.PID LEFT JOIN t_enterprise_dept d on t6.DepartmentID=d.ID LEFT JOIN t_user_info i on t6.PID=i.PID $where GROUP BY $go ORDER BY $go {$limit}";
            $res = $this->querylist($sql);
        }
        return $res;
    }
    //获取所有部门
    public function get_dept()
    {
        $sql = "select ID,Name,ParentID from t_enterprise_dept";
        $res = $this->querylist($sql);
        return $res;
    }
    //获取所有人员
    public function get_user()
    {
        $sql = "select PID,UserName from t_user";
        $res = $this->querylist($sql);
        return $res;
    }
    //
    public function get_dept_user($deptid)
    {
        $sql ="select PID,UserName from t_user where DepartmentID=$deptid";
        $res = $this->querylist($sql);
        return $res;
    }
    //设备巡检任务完成情况（巡检）
    public  function get_device($starttime,$endtime,$arr)
    {
        //查询设备巡检岗位完成量明细
        $sql = "SELECT count(IF(s.donetime <> 0 AND s.donetime<=s.endtime,true,null)) as wc,count(b.workid) as jd,b.workname,b.workid,a.id, a.result, a.pubtime, b.name, b.place, b.indexparam, b.status,a.devid 
					FROM t_bjnz_device_inspecttask a 
					LEFT JOIN t_bjnz_device b ON b.id =  a.devid 
					LEFT JOIN t_bjnz_device_task_sub s ON a.id =s.taskid
					WHERE s.type =1 AND a.status =2 AND s.status=1 AND s.pid<>3245 AND s.optime>=$starttime AND s.optime<=$endtime AND b.workid IN($arr)
					GROUP BY b.workid
					ORDER BY a.pubtime DESC";
        $res = $this->querylist($sql);
        return $res;
    }
    //查询人员岗位脱岗次数
    function  get_work_count($where)
    {
        $sql = "select *, count(if(aa.sub=0, true,null)) as count from (
                select s.taskid,t.postid,
                count(IF(s.pubtime<=s.starttime AND s.starttime<=(s.pubtime+108000),s.taskid,null)) as sub
                FROM  t_bjnz_train_task t 
                LEFT JOIN t_bjnz_train_task_sub s ON t.id = s.taskid 
                {$where}
                GROUP BY s.taskid,t.postid
                ORDER BY s.taskid desc) aa
                GROUP BY aa.postid
                ORDER BY aa.taskid;";
        $res = $this->querylist($sql);
        return $res;
    }
	//各岗位完成流水量单量
    function work_complete_quantity($starttime,$endtime,$arr)
    {
        //查询各岗位完成量明细
        $sql = "select aa.postid,aa.name,sum(aa.sub) as jd,sum(aa.send) as wc from (
                SELECT t.postid,w.name,count(IF(s.workstatus>0,t.postid,null)) as sub,count(IF(s.workstatus=2,t.postid,null)) as send
		    	FROM  t_bjnz_train_task t 
				LEFT JOIN t_bjnz_train_task_sub s ON t.id = s.taskid  
				LEFT JOIN t_bjnz_work w ON t.postid=w.id
                LEFT JOIN t_user u on s.accepitid=u.PID
                LEFT JOIN t_user_info i ON u.PID=i.PID
			    WHERE t.status >3 and s.pubtime>=$starttime and s.pubtime<=$endtime and t.postid IN($arr) 
			    GROUP BY t.postid,s.accepitid order by t.postid desc) as aa
				GROUP BY aa.postid
				ORDER BY aa.postid desc";
        $res = $this->querylist($sql);
        return $res;
    }
	//获取所有岗位
	function get_work($v=1){
	    if($v==2)
        {
            //获取设备巡检岗位
            $sql = "SELECT id,name,isopen FROM t_bjnz_work WHERE id IN(48,49,52,53,54,55,56,57,58,59) ORDER BY CAST(name AS DECIMAL) asc,convert(name USING gbk) asc";
        }
        else
        {
            //获取作业岗位
            $sql = "SELECT id,name,isopen FROM t_bjnz_work WHERE id IN(1,11,22,30,31,34,35,36,44,50,60,61,63,64,65,70,71,72) ORDER BY CAST(name AS DECIMAL) asc,convert(name USING gbk) asc";
        }
        $res = $this->querylist($sql);
        return $res;
	}
	//获取所有网格
	function get_map(){
//        $sql = "SELECT id,area FROM t_bjnz_map ORDER BY CAST(area AS DECIMAL) asc,convert(area USING gbk) asc";
        $sql = "SELECT id,area FROM t_bjnz_map WHERE is_work=1 ORDER BY w_bianhao asc";//按照编号进行排序
        $res = $this->querylist($sql);
        return $res;
	}
	//获取各网格任务量
    function  get_maptask($starttime,$endtime,$arr)
    {
        //num任务量，done完成量
//        $sql = "select m.area,t.addtime,t.mid,count(if(t.type>0,true,null)) as rw,count(if(t.type>1,true,null)) as wc from t_bjnz_map_task t LEFT JOIN t_bjnz_map m on t.mid=m.id
//                where t.type>0 and t.addtime>=$starttime and t.addtime<=$endtime and m.id IN($arr)
//                GROUP BY t.mid
//                ORDER BY t.mid";
//        $sql ="select m.area,t.map,t.tid,t.mid from t_bjnz_map_task t LEFT JOIN t_bjnz_map m on t.mid=m.id where m.id IN($arr) and t.addtime>=$starttime and t.addtime<=$endtime";

        $sql ="select m.area,s.wangge_id as mid,count(IF(s.workstatus>0,true,null)) as rw,count(IF(s.workstatus>1,true,null)) as wc from t_bjnz_train_task t LEFT JOIN t_bjnz_train_task_sub s on t.id=s.taskid LEFT JOIN t_bjnz_map m on s.wangge_id=m.id where s.workstatus>=1 and t.tasktype=0 and s.pubtime>=$starttime and s.pubtime<=$endtime and s.wangge_id IN($arr) group by s.wangge_id";
        $res = $this->querylist($sql);
        return $res;
//逻辑需要只查询map_task表。
//        $arr=array();
//        foreach ($res as $key=>$value)
//        {
//            if($value['map']==1)//任务表t_bjnz_task_sub
//            {
//                $sql ="select TaskId,if(Status>0,true,0) as rw,if(Status=1,true,0) as wc from t_bjnz_task_sub where TaskId=$value[tid] group by TaskId ORDER BY TaskId";
//                $r = $this->query_one($sql);
//            }
//            elseif($value['map']==2)//作业任务表t_bjnz_train_task_sub
//            {
//                $sql ="select taskid,if(workstatus>0,true,0) as rw,if(workstatus=2,true,0) as wc from t_bjnz_train_task_sub where taskid=$value[tid] group by taskid ORDER BY taskid";
//                $r = $this->query_one($sql);
//            }
//            $id = intval($value['mid']);
//            if(!isset($arr[$id]['rw']))
//            {
//                $arr[$id]['rw'] = 0;
//            }
//            if(!isset($arr[$id]['wc']))
//            {
//                $arr[$id]['wc'] = 0;
//            }
//            if(!isset($arr[$id]['area']))
//            {
//                $arr[$id]['area'] = $value['area'];
//            }
//            if(!isset($arr[$id]['mid']))
//            {
//                $arr[$id]['mid'] = $value['mid'];
//            }
//            $arr[$id]['rw'] += $r['rw'];
//            $arr[$id]['wc'] += $r['wc'];
//        }
//        return $arr;
    }
    //车站干部网格巡视统计表
    function  get_wangge_list($star,$end)
    {
        $sql ="select t2.*,u.UserName,u.LoginName,IF(de.Level<>1,de.Name,de.syncDepId) as NAME,
	count(
		IF (
			me.Type = 3
			AND me.CreateTime >= $star
			AND me.CreateTime <= $end,
			TRUE,
			NULL
		)
	) weis,
	count(
		IF (
			me.Type <>3
			AND me.CreateTime >= $star
			AND me.CreateTime <= $end,
			TRUE,
			NULL
		)
	) anq
from 
(
SELECT
	t1.PID,	t1.map_name,count(
		IF (
			dm. STATUS >=0
			AND dm.addtime >= $star
			AND dm.addtime <= $end,
			true,
			NULL
		)
	) AS baoxiu
FROM
	(
		SELECT
			a.PID,
			a.addtime,
			GROUP_CONCAT(DISTINCT map.area) AS map_name
		FROM
			bjnz_new_quantification_user_map_add a
		LEFT JOIN bjnz_new_quantification_user_map m ON a.qum_id = m.id
		LEFT JOIN bjnz_new_quantification_user u ON m.qu_user_id = u.id
		LEFT JOIN t_bjnz_map map ON a.mapid = map.id
		WHERE
			a.type = 2
		AND m.isdel = 1
		AND u. STATUS = 1
                          AND  a.addtime >= $star
                          AND a.addtime <= $end
		GROUP BY
			a.PID
	) t1
LEFT JOIN t_bjnz_device_maintenance dm ON t1.PID = dm.adduid
GROUP BY
	t1.PID
) t2
LEFT JOIN t_bjnz_report_mess me ON t2.PID = me.Cid
LEFT JOIN t_user u ON t2.PID = u.PID
LEFT JOIN t_enterprise_dept de ON u.DepartmentID = de.ID
GROUP BY t2.PID";
        $res = $this->querylist($sql);
        return $res;
    }
    //设备报修情况统计表
    function  get_device_list($star,$end)
    {
        $sql = "select d.status,d.typename,d.name,map.area as wangge_id,m.remark,m.addname,FROM_UNIXTIME(m.addtime,'%Y-%m-%d %H:%i:%S') as addtime,d.dep_name,m.filepath from t_bjnz_device_maintenance m LEFT JOIN t_bjnz_device d ON m.devid = d.id LEFT JOIN t_bjnz_map map on d.wangge_id=map.id where m.addtime>=$star and m.addtime<=$end";
        $res = $this->querylist($sql);
        return $res;
    }
    //环境卫生上报统计表
    function  get_hygiene_list($star,$end)
    {
        $sql = "select m.Contents,m.Title,FROM_UNIXTIME(m.CreateTime,'%Y-%m-%d %H:%i:%S') as CreateTime,map.area,t.UserName,IF(d.Level<>1,d.Name,d.syncDepId) as Name from t_bjnz_report_mess m LEFT JOIN t_user t on m.Cid=t.PID LEFT JOIN t_enterprise_dept d on t.DepartmentID=d.ID LEFT JOIN t_bjnz_map map on m.wangge_id=map.id where m.Type=3 and m.CreateTime>=$star and m.CreateTime<=$end";
        $res = $this->querylist($sql);
        return $res;
    }
    //安全信息上报统计表
    function  get_security_list($star,$end)
    {
        $sql = "select mt.Name as aqname,m.Title,FROM_UNIXTIME(m.CreateTime,'%Y-%m-%d %H:%i:%S') as CreateTime,m.Contents,map.area,t.UserName,IF(d.Level<>1,d.Name,d.syncDepId) as Name from t_bjnz_report_mess m LEFT JOIN t_user t on m.Cid=t.PID LEFT JOIN t_enterprise_dept d on t.DepartmentID=d.ID LEFT JOIN t_bjnz_map map on m.wangge_id=map.id LEFT JOIN t_bjnz_report_mess_type mt on m.Type=mt.Id where m.Type<>3 and m.CreateTime>=$star and m.CreateTime<=$end";// where m.Type in(34,35,36,37,38,39,40,41)
        $res = $this->querylist($sql);
        return $res;
    }
    //现场职工脱岗情况统计表
    function  get_tuogang_list($star,$end)
    {
        $sql="SELECT t.PID,t.LoginName,m.adduid,t.UserName,IF(de.Level<>1,de.Name,de.syncDepId) as Name,count(IF(m.status>=0,true,null)) as baoxiu,count(IF(m.wangge_id<>d.wangge_id,true,NULL)) as tuogang,map.area as wangge FROM t_bjnz_device_maintenance m
            LEFT JOIN t_bjnz_device d ON m.devid = d.id
            LEFT JOIN t_user t on m.adduid=t.PID 
            LEFT JOIN t_enterprise_dept de on t.DepartmentID=de.ID
            LEFT JOIN t_bjnz_map map on m.wangge_id=map.id
            WHERE m.addtime>=$star and m.addtime<=$end
            GROUP BY
                m.adduid,m.wangge_id
            ORDER BY
                m.adduid";
        $r = $this->querylist($sql);
        if(count($r) == 0)
        {
            return $r;
        }
        $arr=array();
        foreach($r as $value)
        {
            $arr[]=$value['adduid'];
        }
        $arr=implode(",",$arr);
        $sql1 = "select Cid,count(Cid) as anq from t_bjnz_report_mess where Type<>3 and CreateTime>=$star and CreateTime<=$end and Cid IN($arr) GROUP BY Cid";
        $r1 = $this->querylist($sql1);
        $sql2 = "select Cid,count(Cid) as hj from t_bjnz_report_mess where Type=3 and CreateTime>=$star and CreateTime<=$end and Cid IN($arr) GROUP BY Cid";
        $r2 = $this->querylist($sql2);
        $array = array();
        foreach ($r1 as $val)
        {
            $array[$val['Cid']]['anq'] = $val['anq'];
        }
        foreach ($r2 as $val)
        {
            $array[$val['Cid']]['hj'] = $val['hj'];
        }
        foreach ($r as &$v)
        {
            if(isset($array[$v['adduid']]))
            {
                $v['anq'] = isset($array[$v['adduid']]['anq']) ?$array[$v['adduid']]['anq'] :0;
                $v['hj'] = isset($array[$v['adduid']]['hj'])?$array[$v['adduid']]['hj']:0;
            }
            else
            {
                $v['anq']=0;
                $v['hj']=0;
            }
        }
        return $r;
    }
    function get_anq_list($PID,$star,$end)
    {
        $sql ="select mt.Name,count(m.Type) as num from t_bjnz_report_mess m LEFT JOIN t_bjnz_report_mess_type mt on m.Type=mt.Id where m.Type<>3 and m.Cid=$PID and m.CreateTime>=$star and m.CreateTime<=$end group by m.Type";// where m.Type in(34,35,36,37,38,39,40,41)
        $res = $this->querylist($sql);
        return $res;
    }
	//通过对象ID，查询对象
	function get_SendsubById($m_id){
	
		$mod = $this->query($this->table,array('m_id'=>$m_id));
		if(!empty($mod)){
			$mod = $mod[0];
		}
		return $mod;
	}
	//插入记录
	function query_insert($data){
		$onject = $this->insert($this->table,$data);
		return $onject;
	}
	 
	//删除记录
	function query_delete($where){
		 
		$onject = $this->delete($this->table,$where);
		return $onject;
	}
	//修改记录
	function query_update($where,$data){
		 
		$onject = $this->update($this->table,$where,$data);
		return $onject;
	}
}